package fu.abc.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.mail.*;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import fu.abc.model.*;

public class LocationDAO {
	
	Connection connect = null;
	ResultSet result = null;
	Statement state = null;
	PreparedStatement pre = null;
	
//	search by input text and province
	public List<Location> listLocationSearch(String search, String province){
		
		List<Location> list = new ArrayList<>();
		 
		String query = "";
//		search by keyword and province
		String  sqlsearchpro = "SELECT DISTINCT idlocal,localname,b.category,c.province,addressDetail,descript,idprovince"
				+ " FROM LOCATION a,CATEGORY b,PROVINCE c"
				+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND rtrim(dbo.ufn_catdau([localname])) like N'%"+search+"%' "
						+ " or freetext(localname,N'"+search+"') AND idprovince="+province+" AND [status]=1";
//		search by province
		String sqlprovince = "SELECT idlocal,localname,b.category,c.province,addressDetail,descript,idprovince"
				+ " FROM LOCATION a,CATEGORY b,PROVINCE c"
				+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND idprovince="+province+" AND [status]= 1";
//		search by keyword 
		String sqlsearch = "SELECT DISTINCT idlocal,localname,b.category,c.province,addressDetail,descript,idprovince, a.[status]"
				+ " FROM LOCATION a,CATEGORY b,PROVINCE c"
				+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND a.[status] = 1 AND rtrim(dbo.ufn_catdau([localname])) like N'%"+search+"%' "
						+ " or freetext(localname,N'"+search+"')";
//		search null
//		String sqlsearch = ""; 
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
//				search location by province
				if(("".equals(search) || search==null) && (!"".equals(province) || province!=null)){
					query = sqlprovince;
//				search location by text and province
				}else if((!"".equals(search) || search!=null) && (!"".equals(province) || province!=null)){
					query = sqlsearchpro;
//				search location by search
				}else if((!"".equals(search) || search!=null) && ("".equals(province) || province==null)){
					query = sqlsearch;
				}
				state = connect.createStatement();
				result = state.executeQuery(query);
				
				while(result.next()){
					Location local = new Location();
					local.setIdNewLocal(result.getInt("idlocal"));
					local.setLocationName(result.getString("localname"));
					local.setCategory(result.getString("category"));
					local.setProvince(result.getString("province"));
					local.setAddressDetail(result.getString("addressDetail"));
					local.setDescript(result.getString("descript"));
					list.add(local);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	// create location
	public boolean createLocationUser (Location location) throws SQLException {
		
		
		String query = "INSERT INTO LOCATION(localname,category,province,addressDetail,phone,website,descript,[status],mode)"
						+ " VALUES(?,?,?,?,?,?,?,?,?)";
		String sql1 = "SELECT max(idlocal) as [idmax] FROM LOCATION";
		String sql2 = "INSERT INTO USER_LOCATION VALUES(?,?,?)"; 
		String sql3 = "INSERT INTO IMAGE_LOCATION VALUES(?,?)";
		
		int numb = 0;
//		connect to DB
		connect = UtilConnection.getConnection();
		if(connect != null){
				try {		
//			insert into LOCATION
				pre = connect.prepareStatement(query);
				pre.setString(1, location.getLocationName());
				pre.setString(2, location.getCategory());
				pre.setString(3, location.getProvince());
				pre.setString(4, location.getAddressDetail());
				pre.setString(5, location.getPhone());
				pre.setString(6, location.getWebsite());
				pre.setString(7, location.getDescript());
				pre.setInt(8, location.getStatus());
				pre.setBoolean(9, location.getMode());
				pre.execute();
//			SELECT id from LOCATION 
				state = connect.createStatement();
				result = state.executeQuery(sql1);
				while(result.next()){
					numb = result.getInt("idmax");
				}
//			INSERT into USER_LOCATION
				pre = connect.prepareStatement(sql2);
				pre.setString(1, location.getEmail());
				pre.setInt(2, location.getEncode());
				pre.setInt(3, numb);
				pre.execute();
//			INSERT INTO IMAGE_LOCATION
				pre = connect.prepareStatement(sql3);
				pre.setInt(1, numb);
				pre.setString(2, location.getLinkimage());
				pre.execute();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
		}else 
			return false;
	}
	
	// create location by user
	
	public boolean createLocation (Location location) throws SQLException {
		

		String query = "INSERT INTO LOCATION(localname,category,province,addressDetail,phone,website,descript,[status],mode)"
						+ " VALUES(?,?,?,?,?,?,?,?,?)";
		String sql1 = "SELECT max(idlocal) as [idmax] FROM LOCATION";
		String sql2 = "INSERT INTO EDITOR_LOCATION VALUES(?,?)"; 
		
		int numb = 0;
//		connect to DB
		connect = UtilConnection.getConnection();
		if(connect != null){
				try {		
		//	insert into LOCATION
				pre = connect.prepareStatement(query);
				pre.setString(1, location.getLocationName());
				pre.setString(2, location.getCategory());
				pre.setString(3, location.getProvince());
				pre.setString(4, location.getAddressDetail());
				pre.setString(5, location.getPhone());
				pre.setString(6, location.getWebsite());
				pre.setString(7, location.getDescript());
				pre.setInt(8, location.getStatus());
				pre.setBoolean(9, location.getMode());
				pre.execute();
		//	SELECT id from LOCATION 
				state = connect.createStatement();
				result = state.executeQuery(sql1);
				while(result.next()){
					numb = result.getInt("idmax");
				}
		//	INSERT into USER_LOCATION
				pre = connect.prepareStatement(sql2);
				pre.setInt(1, numb);
				pre.setInt(2, location.getIdacc());
				pre.execute();
				
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}finally{
				if(connect!=null){
//					close connect
					connect.close();
				}
			}
		}else 
			return false;
	}
	
//	get infor after create location
	
	public Location getLocalNew(int id) throws SQLException{
		
		Location local = new Location();
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
				String query = "SELECT idlocal,localname,category,province,addressDetail,phone,website,descript,latLnglat,latLnglng"
						+ " FROM"
						+ " (SELECT idlocal,localname,b.category, c.province,addressDetail,phone,website,descript,latLnglat,latLnglng"
						+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
						+ " WHERE a.category = b.idcate AND a.province = c.idprovince) as A"
						+ " WHERE idlocal = '"+id+"' ";
				
				state = connect.createStatement();
//				execute query
				result = state.executeQuery(query);
//				get information
				while(result.next()){
					local.setLocationName(result.getString("localname"));
					local.setCategory(result.getString("category"));
					local.setProvince(result.getString("province"));
					local.setAddressDetail(result.getString("addressDetail"));
					local.setPhone(result.getString("phone"));
					local.setWebsite(result.getString("website"));
					local.setDescript(result.getString("descript"));
					local.setEmail(result.getString("email"));
					local.setLinkimage(result.getString("imagelink"));
					local.setLatLnglat(result.getFloat("latLnglat"));
					local.setLatLnglng(result.getFloat("latLnglng"));
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
		
		return local;		
	}
	
//	get infor after create location
	
	public Location getLocalForUser(int id) throws SQLException{
		
		Location local = new Location();
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
				String query = "SELECT idlocal,localname,category,province,addressDetail,phone,website,descript,email,encode,latLnglat,latLnglng"
						+ " FROM"
						+ " (SELECT idlocal,localname,b.category, c.province,addressDetail,phone,website,descript,d.encode,d.email,latLnglat,latLnglng"
						+ " FROM LOCATION a, CATEGORY b, PROVINCE c ,USER_LOCATION d"
						+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND a.idlocal=d.location) as A"
						+ " WHERE idlocal = '"+id+"' ";
				
				state = connect.createStatement();
//				execute query
				result = state.executeQuery(query);
//				get information
				while(result.next()){
					local.setLocationName(result.getString("localname"));
					local.setCategory(result.getString("category"));
					local.setProvince(result.getString("province"));
					local.setAddressDetail(result.getString("addressDetail"));
					local.setPhone(result.getString("phone"));
					local.setWebsite(result.getString("website"));
					local.setDescript(result.getString("descript"));
					local.setEmail(result.getString("email"));
					local.setEncode(result.getInt("encode"));
					local.setLatLnglat(result.getFloat("latLnglat"));
					local.setLatLnglng(result.getFloat("latLnglng"));
//					local.setLinkimage(result.getString("imagelink"));
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
		
		return local;		
	}
	
//	get information location by localName
//	
//	public Location getLocalbyName(String localname) throws SQLException{
//		
//		Location local = new Location();
//		try {
////			connect to DB
//			connect = UtilConnection.getConnection();
//			if(connect!=null){
////				query select from DB
//				String query = "SELECT * FROM LOCATION WHERE localName = '"+localname+"'";
//				pre = connect.prepareStatement(query);
//				result = pre.executeQuery();
////				get information
//				while(result.next()){
//					local.setIdNewLocal(result.getInt("idlocal"));
//					local.setLocationName(result.getString("localName"));
//					local.setCategory(result.getString("category"));
//					local.setProvince(result.getString("province"));
//					local.setAddressDetail(result.getString("addressDetail"));
//					local.setPhone(result.getString("phone"));
//					local.setWebsite(result.getString("website"));
//					local.setDescript(result.getString("descript"));
//				}
//			}
//			
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}finally{
//			if(connect!=null){
//				connect.close();
//			}
//		}
//		
//		return local;		
//	}
	
//	search location
	public List<Location> searchlocal(String search, String category, String province) {
		 
        String query ="";
        List<Location> list = new ArrayList<>();
        
//        list all Location
        String listAll = "SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0 "
        		+ " ORDER BY idlocal DESC";
        
//        list all by category
        String listAllByCate = "SELECT idlocal,idprovince,idcate,localname,category, province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,localname,b.category, c.province,addressDetail,[status],idprovince,mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0) as A"
        		+ " WHERE idcate = "+category+""
        				+ " ORDER BY idlocal DESC";
       
//        list all by search
        String listAllBySearch = "SELECT idlocal,idcate,idprovince,localname,category, province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0) as A"
        		+ " WHERE localname LIKE '%"+search+"%' or rtrim(dbo.ufn_catdau([localname])) like N'%"+search+"%'"
        				+ " ORDER BY idlocal DESC";
        
//        list all by province
        String listAllByProvince = "SELECT idlocal,idcate,idprovince,localname,category, province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0) as A"
        		+ " WHERE idprovince = "+province+""
        				+ " ORDER BY idlocal DESC";
        
//        list all by search + category
        String listAllBySearchCate = "SELECT idlocal,idcate,idprovince,localname,category,province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0) as A"
        		+ " WHERE localname LIKE '%"+search+"%' or rtrim(dbo.ufn_catdau([localname])) like N'%"+search+"%' AND idcate = "+category+""
        				+ " ORDER BY idlocal DESC";
        
//        list all by search + province
        String listAllBySearchPro = "SELECT idlocal,idcate,idprovince,localname,category, province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0) as A"
        		+ " WHERE localname LIKE '%"+search+"%' or rtrim(dbo.ufn_catdau([localname])) like N'%"+search+"%' AND idprovince ="+province+""
        				+ " ORDER BY idlocal DESC";
        
//        list all by category + province
        String listAllByCategoryPro =  "SELECT idlocal,idcate,idprovince,localname,category, province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince  AND [status]<>0) as A"
        		+ " WHERE idcate = "+category+" AND idprovince = "+province+""
        				+ " ORDER BY idlocal DESC";
                
//        list all by search + category + province
        String listAllBySearchCategoryPro =  "SELECT idlocal,idcate,idprovince,localname,category, province,addressDetail,[status],mode"
        		+ " FROM"
        		+ " (SELECT idlocal,idcate,idprovince,localname,b.category, c.province,addressDetail,[status],mode"
        		+ " FROM LOCATION a, CATEGORY b, PROVINCE c"
        		+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND [status]<>0) as A"
        		+ " WHERE localname LIKE '%"+search+"%' or rtrim(dbo.ufn_catdau([localname])) like N'%"+search+"%' AND idcate = "+category+" AND idprovince ="+province+""
        				+ " ORDER BY idlocal DESC";
        try {

        	connect = UtilConnection.getConnection();
        	state = connect.createStatement();
        	
//        	if input to search
//          list all Location
            if (("".equals(search)||search==null) && ("".equals(category)||category==null) && ("".equals(province)||province==null)) {
            	query = listAll;
            }//        list all by category
            else if(("".equals(search) || search==null) && (!"".equals(category) || category!=null) && ("".equals(province)||province==null)){
            	query = listAllByCate;
            }//        list all by search
            else if((!"".equals(search) || search!=null) && ("".equals(category) || category==null) && ("".equals(province)||province==null)){
            	query = listAllBySearch;
            }//        list all by province
            else if(("".equals(search) || search==null) && ("".equals(category) || category==null) && (!"".equals(province)||province!=null)){
            	query = listAllByProvince;
            }//        list all by search + category
            else if((!"".equals(search) || search!=null) && (!"".equals(category) || category!=null) && ("".equals(province)||province==null)){
            	query = listAllBySearchCate;
            }//        list all by search + province
            else if((!"".equals(search) || search!=null) && ("".equals(category) || category==null) && (!"".equals(province)||province!=null)){
            	query = listAllBySearchPro;
            }//        list all by category + province
            else if(("".equals(search) || search==null) && (!"".equals(category) || category!=null) && (!"".equals(province)||province!=null)){
            	query = listAllByCategoryPro;
            }//        list all by search + category + province
            else if(("".equals(search) || search==null) && ("".equals(category) || category==null) && (!"".equals(province)||province!=null)){
            	query = listAllBySearchCategoryPro;
            }
            	
            	result = state.executeQuery(query); //   execute query
                while (result.next()) {
                	Location local = new Location();
                	local.setIdNewLocal(result.getInt("idlocal"));
    				local.setLocationName(result.getString("localname"));
    				local.setCategory(result.getString("category"));
    				local.setProvince(result.getString("province"));
    				local.setAddressDetail(result.getString("addressDetail"));
    				local.setMode(result.getBoolean("mode"));
    				local.setStatus(result.getInt("status"));
//    				add local to list
    				list.add(local);
                }
        } catch (SQLException ex) {

        }
        return list;
    }
	
// get new location
	public List<Location> listNewLocation(){
		
		List<Location> list = new ArrayList<>();
		String sqlsearch = "SELECT top 5 idlocal,localname FROM LOCATION ORDER BY idlocal DESC";
		
		try {
			connect = UtilConnection.getConnection();
			
			if(connect!=null){

				state = connect.createStatement();
				result = state.executeQuery(sqlsearch);
				
				while(result.next()){
					Location local = new Location();
					local.setIdNewLocal(result.getInt("idlocal"));
					local.setLocationName(result.getString("localname"));
					list.add(local);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
//	edit location
	
	public void updateLocation(Location local){
		
		String query = "UPDATE LOCATION SET localname=?,category=?,province=?,addressDetail=?,phone=?,website=?,descript=? WHERE idLocal=?";
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
				pre = connect.prepareStatement(query);
				pre.setString(1, local.getLocationName());
				pre.setString(2, local.getCategory());
				pre.setString(3, local.getProvince());
				pre.setString(4, local.getAddressDetail());
				pre.setString(5, local.getPhone());
				pre.setString(6, local.getWebsite());
				pre.setString(7, local.getDescript());
				pre.setInt(8, local.getIdNewLocal());
				
				pre.executeUpdate();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	
// update location by user
	
	public void updateLocationByUser(Location local){
		
		String query1 = "UPDATE USER_LOCATION SET email=? WHERE location=?";
		String query2 = "SELECT location FROM USER_LOCATION WHERE encode LIKE '%"+local.getNumber()+"%'";
		String query3 = "UPDATE LOCATION SET localname=?,category=?,province=?,addressDetail=?,phone=?,website=?,descript=? WHERE idlocal=?";
		String query4 = "UPDATE IMAGE_LOCATION SET imagelink=? WHERE location=?";
		
		int location=0;
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
//				execute query select location
				state = connect.createStatement();
				result = state.executeQuery(query2);
				while(result.next()){
					location = result.getInt("location");
				}
				
//				execute query update to email
				pre = connect.prepareStatement(query1);
				pre.setString(1, local.getEmail());
				pre.setInt(2, location);
				pre.executeUpdate();
				
//				execute query update to Location
				pre = connect.prepareStatement(query3);
				pre.setString(1, local.getLocationName());
				pre.setString(2, local.getCategory());
				pre.setString(3, local.getProvince());
				pre.setString(4, local.getAddressDetail());
				pre.setString(5, local.getPhone());
				pre.setString(6, local.getWebsite());
				pre.setString(7, local.getDescript());
				pre.setInt(8, location);
				pre.executeUpdate();
				
//				execute query update to linkimage
				pre = connect.prepareStatement(query4);
				pre.setString(1, local.getLinkimage());
				pre.setInt(2, location);
				pre.executeUpdate();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	
// get infor location by encode
	
public Location getLocalByEncode(int encode) throws SQLException{
		
		Location local = new Location();
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
				String query = "SELECT idlocal,localname,category,province,addressDetail,phone,website,descript,encode,email"
						+ " FROM"
						+ " (SELECT idlocal,localname,b.category, c.province,addressDetail,phone,website,descript,d.encode,d.email"
						+ " FROM LOCATION a, CATEGORY b, PROVINCE c, USER_LOCATION d"
						+ " WHERE a.category = b.idcate AND a.province = c.idprovince AND a.idlocal=d.location) as A"
						+ " WHERE encode = '"+encode+"' ";
				
				state = connect.createStatement();
//				execute query
				result = state.executeQuery(query);
//				get information
				while(result.next()){
					local.setLocationName(result.getString("localname"));
					local.setCategory(result.getString("category"));
					local.setProvince(result.getString("province"));
					local.setAddressDetail(result.getString("addressDetail"));
					local.setPhone(result.getString("phone"));
					local.setWebsite(result.getString("website"));
					local.setDescript(result.getString("descript"));
//					local.setLinkimage(result.getString("imagelink"));
					local.setEmail(result.getString("email"));
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
		
		return local;		
	}
	
//	cancel loation 
	
	public void cancelLocation(int id){
		
		try {
			connect = UtilConnection.getConnection();
			if(connect!=null){
				String query ="UPDATE LOCATION SET [status]= 0 WHERE idlocal=?";
				pre = connect.prepareStatement(query);
				pre.setInt(1, id);
				pre.executeUpdate();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	// get link crawler
	public List<Link> listLink(String cate){
		
		List<Link> list = new ArrayList<>();
 		String query ="";
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				if("".equals(cate) || cate==null){
					query = "SELECT idlink,b.category,keyword,link,[status] FROM SOURCE_LINK a, CATEGORY b "
							+ " WHERE a.category = b.idcate";
				}else if(!"".equals(cate) || cate!=null){
					query = "SELECT idlink,b.category,keyword,link,[status] FROM SOURCE_LINK a, CATEGORY b "
							+ " WHERE a.category = b.idcate and a.category='"+cate+"'";
				}
				
				state = connect.createStatement();
//				execute query
				result = state.executeQuery(query);
				while(result.next()){
					Link link = new Link();
					link.setIdlink(result.getInt("idlink"));
					link.setCategory(result.getString("category"));
					link.setKeyword(result.getString("keyword"));
					link.setLink(result.getString("link"));
					link.setStatus(result.getInt("status"));
//					add link to list
					list.add(link);
				}
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	// get image for location
	
	public List<Location> getImageLocation (int location) throws SQLException{
		
		List<Location> list = new ArrayList<>();
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
//				query select from DB
				String query = "SELECT imagelink FROM IMAGE_LOCATION WHERE location='"+location+"'";
				pre = connect.prepareStatement(query);
				result = pre.executeQuery();
//				get information
				while(result.next()){
					Location local = new Location();
					local.setLinkimage(result.getString("imagelink"));
					list.add(local);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
		
		return list;		
	}
	
//  get email
	
	public Location getEmail(int id) throws SQLException{
		
		Location local = new Location();
		String query = "SELECT email FROM USER_LOCATION WHERE location="+id+"";
//		connect to DB
		connect = UtilConnection.getConnection();
		if(connect!=null){
//			excute query
			pre = connect.prepareStatement(query);
			result = pre.executeQuery();
			while(result.next()){
//				get email
				local.setEmail(result.getString("email"));
			}
			
		}
		return local;
	}
	
// validate Location
	
	public void validateLocation(Location local){
		
		String query = "UPDATE LOCATION SET [status]=? WHERE idlocal=?";
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			pre = connect.prepareStatement(query);
			if(connect!=null){
				
				pre.setInt(1, local.getStatus());
				pre.setInt(2, local.getIdNewLocal());
				pre.executeUpdate();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	// send mail
	public void sendEmail(String mailport,String from, String fromPass, String fromhost,
			String to, String subject, String msg) {
		
		
		if ("".equals(from) || "".equals(fromhost) || "".equals(fromPass)||"".equals(mailport))
			return;
		final String user = from;
		final String pass = fromPass;
		// 1st step) Get the session object
		Properties  props = new Properties();
		props.put("mail.smtp.host", fromhost);// change accordingly
		props.put("mail.smtp.auth", "true");
		props.put("mail.smtp.port", mailport);
		props.put("mail.smtp.starttls.enable", true);
		Session session = Session.getDefaultInstance(props,
				new javax.mail.Authenticator() {
					protected PasswordAuthentication getPasswordAuthentication() {
						return new PasswordAuthentication(user, pass);
					}
				});
		// 2nd step)compose message
		try {
			MimeMessage message = new MimeMessage(session);
			message.setFrom(new InternetAddress(user));
			message.addRecipient(Message.RecipientType.TO, new InternetAddress(
					to));
			message.setSubject(subject);
			message.setText(msg);

			// 3rd step)send message
			Transport.send(message);
		} catch (javax.mail.MessagingException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
}
